﻿using System;
using System.Collections;
using System.Data;
using System.Reflection;
using System.Windows.Forms;

namespace XKControlLib.ExportExcel
{
    public class DataToExcel
    {
        public static void ToExcel(DataTable dt)
        {
            try
            {
                Type objClassType = Type.GetTypeFromProgID("Excel.Application");
                object objExcel = Activator.CreateInstance(objClassType);
                var Fd = new SaveFileDialog();
                Fd.Filter = "*.xlsx| *.xlsx";
                if (Fd.ShowDialog() == DialogResult.OK) {
                    object objBooks = objExcel.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objExcel, null);
                    object objBook = objBooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks, null);
                    var objSheets = objBook.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook, null);
                    var objSheet = objSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets, new Object[] { 1 });

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (dt.Columns[i].DataType == typeof(string))
                        {
                            var objCol = objSheet.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, objSheet, new Object[] { i + 1 });
                            objCol.GetType().InvokeMember("NumberFormatLocal", BindingFlags.SetProperty, null, objCol, new Object[] { "@" });
                        }
                        if (dt.Columns[i].DataType ==typeof(int) || dt.Columns[i].DataType== typeof(long)){
                        var objCol = objSheet.GetType().InvokeMember("Columns", BindingFlags.GetProperty, null, objSheet, new Object[] { i + 1 });
                                objCol.GetType().InvokeMember("NumberFormatLocal", BindingFlags.SetProperty, null, objCol, new Object[] { "0" });
                          }
                        var cColumnName= dt.Columns[i].Caption;
                        var RowIndex = 1;
                        var ColumnIndex = i + 1;
                        var objCell = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { RowIndex, ColumnIndex });
                        objCell.GetType().InvokeMember("NumberFormatLocal", BindingFlags.SetProperty, null, objCell, new Object[] { "@" });
                        objCell.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objCell, new Object[] { cColumnName });
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Application.DoEvents();
                        var objCell1 = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { i + 2, 1 });
                        var objCell2 = objSheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, objSheet, new Object[] { i + 2, dt.Columns.Count });
                        var objRange = objSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet, new Object[] { objCell1, objCell2 });
                        objSheet.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange, new Object[] { dt.Rows[i].ItemArray });
                    }

                    objBook.GetType().InvokeMember("SaveAs", BindingFlags.GetProperty, null, objBook, new Object[] { Fd.FileName });
                    objBook.GetType().InvokeMember("Close", BindingFlags.GetProperty, null, objBook, new Object[] { });
                    objExcel.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, objExcel, new Object[] { false });
                    objExcel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, objExcel, null);
                    MessageBox.Show("共有" + dt.Rows.Count + "条记录被导出");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            
        }

        public static void ToExcel(DataView dv)
        {
            var dt = dv.ToTable();
            ToExcel(dt);
        }

        public static void ToExcel(Array array)
        {
            if (array != null)
            {
                if (array.Length != 0)
                {
                    DataTable dt = ToTable(array, array.GetType());
                    ToExcel(dt);
                }
            }
        }

        public static void ToExcel(IList list)
        {
            if (list != null)
            {
                if (list.Count != 0)
                {
                    DataTable dt = ToTable(list, list[0].GetType());
                    ToExcel(dt);
                }
            }
        }

        private static DataTable ToTable(Type type)
        {
            DataTable dt=new DataTable();
            foreach (PropertyInfo Pro in type.GetProperties())
            {
                var col = new DataColumn(Pro.Name, Pro.PropertyType);
                dt.Columns.Add(col);

            }
            return dt;
        }

        private static DataTable ToTable(IList list, Type type)
        {
            var Arr = new object[list.Count];
            list.CopyTo(Arr, 0);
            return ToTable(Arr, type);
        }

        private static DataTable ToTable(Array array, Type type)
        {
            var dt = ToTable(type);
            var Pros = type.GetProperties();
            foreach (var obj in array)
            {
                var dr = dt.NewRow();
                ToDataRow(obj, dr, Pros);
                dt.Rows.Add(dr);
            }
            return dt;
        }
        
        private static DataTable ToTable(DataGridViewColumnCollection cols)
        {
            DataTable dt = new DataTable();
            foreach (DataGridViewColumn col in cols)
            {
                var cl = new DataColumn();
                cl.ColumnName = col.Name;
                cl.Caption = col.HeaderText;
                if (col.ValueType != null)
                {
                    cl.DataType = col.ValueType;
                }
                dt.Columns.Add(cl);
            }
            return dt;
        }

        private static void ToDataRow(object obj, DataRow dr)
        {
            var Pros = obj.GetType().GetProperties();
            ToDataRow(obj, dr, Pros);
        }

        private static void ToDataRow(object obj, DataRow dr, PropertyInfo[] pros)
        {
            foreach (PropertyInfo p in pros)
            {
                try
                {
                    if (dr.Table.Columns.Contains(p.Name))
                    {
                        var value = p.GetValue(obj, null);
                        if (value == null && p.PropertyType==typeof(DateTime))
                        {
                            dr[p.Name] = DBNull.Value;
                        }
                        else
                        {
                            dr[p.Name] = value;
                        }
                    }
                   
                }
                catch (Exception)
                {
                }
            }
        }
    }
}
